package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.AccountDao;
import com.clqteam.model.Account;
import com.clqteam.util.JDBCutil;

public class AccountDaoImpl implements AccountDao {

	@Override
	public List<Account> getAccount(int page, int rows) {
		List<Account> accounts = new ArrayList<Account>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT s_accountant.aId,s_accountant.oId,s_orderclass.oClassName,");
		sql.append(" s_orderclass.oClassType,s_orderclass.oClassTypeID,s_order.oMoney,");
		sql.append(" s_order.oTime,s_order.eId,s_employee.eName FROM s_accountant");
		sql.append(" INNER JOIN s_order ON s_order.oId = s_accountant.oId");
		sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
		sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
		sql.append(" LIMIT ?,?");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Account account = new Account();
				account.setaId(rs.getInt("aId"));
				account.setoId(rs.getInt("oId"));
				account.setoClassName(rs.getString("oClassName"));
				account.setoClassType(rs.getString("oClassType"));
				account.setoClassTypeID(rs.getInt("oClassTypeID"));
				account.setoMoney(rs.getDouble("oMoney"));
				account.setoTime(rs.getString("oTime"));
				account.seteId(rs.getInt("eId"));
				account.seteName(rs.getString("eName"));
				accounts.add(account);
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
		return accounts;
	}

	@Override
	public int getCountAccount() {
		int total = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(s_accountant.aId) AS total FROM s_accountant");
		Connection con = JDBCutil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}				
		return total;
	}

	@Override
	public List<Account> getAccountByKeyAndDT(String key, String begindt, String enddt, int page, int rows) {
		List<Account> accounts = new ArrayList<Account>();
		Connection con = JDBCutil.getConnection();
		try {
			int k = Integer.parseInt(key);
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT s_accountant.aId,s_accountant.oId,s_orderclass.oClassName,");
			sql.append(" s_orderclass.oClassType,s_orderclass.oClassTypeID,s_order.oMoney,");
			sql.append(" s_order.oTime,s_order.eId,s_employee.eName FROM s_accountant");
			sql.append(" INNER JOIN s_order ON s_order.oId = s_accountant.oId");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE s_order.oTime >= ? AND s_order.oTime <= ? AND s_order.oId = ?");
			sql.append(" LIMIT ?,?");
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, begindt);
				pst.setString(2, enddt);
				pst.setInt(3, k);
				pst.setInt(4, (page-1)*rows);
				pst.setInt(5, rows);
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					Account account = new Account();
					account.setaId(rs.getInt("aId"));
					account.setoId(rs.getInt("oId"));
					account.setoClassName(rs.getString("oClassName"));
					account.setoClassType(rs.getString("oClassType"));
					account.setoClassTypeID(rs.getInt("oClassTypeID"));
					account.setoMoney(rs.getDouble("oMoney"));
					account.setoTime(rs.getString("oTime"));
					account.seteId(rs.getInt("eId"));
					account.seteName(rs.getString("eName"));
					accounts.add(account);
				}	
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		} catch (NumberFormatException eo) {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT s_accountant.aId,s_accountant.oId,s_orderclass.oClassName,");
			sql.append(" s_orderclass.oClassType,s_orderclass.oClassTypeID,s_order.oMoney,");
			sql.append(" s_order.oTime,s_order.eId,s_employee.eName FROM s_accountant");
			sql.append(" INNER JOIN s_order ON s_order.oId = s_accountant.oId");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE s_order.oTime >= ? AND s_order.oTime <= ? AND (");
			sql.append(" s_orderclass.oClassName LIKE ? OR s_orderclass.oClassType LIKE ?");
			sql.append(" OR s_employee.eName LIKE ?)");
			sql.append(" LIMIT ?,?");
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, begindt);
				pst.setString(2, enddt);
				pst.setString(3, "%"+key+"%");
				pst.setString(4, "%"+key+"%");
				pst.setString(5, "%"+key+"%");
				pst.setInt(6, (page-1)*rows);
				pst.setInt(7, rows);
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					Account account = new Account();
					account.setaId(rs.getInt("aId"));
					account.setoId(rs.getInt("oId"));
					account.setoClassName(rs.getString("oClassName"));
					account.setoClassType(rs.getString("oClassType"));
					account.setoClassTypeID(rs.getInt("oClassTypeID"));
					account.setoMoney(rs.getDouble("oMoney"));
					account.setoTime(rs.getString("oTime"));
					account.seteId(rs.getInt("eId"));
					account.seteName(rs.getString("eName"));
					accounts.add(account);
				}	
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}		
		return accounts;
	}

	@Override
	public int getCountAccountByKeyAndDT(String key, String begindt, String enddt) {
		int total = 0;
		try {
			int k = Integer.parseInt(key);
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT COUNT(s_accountant.aId) AS total FROM s_accountant");
			sql.append(" INNER JOIN s_order ON s_order.oId = s_accountant.oId");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE s_order.oTime >= ? AND s_order.oTime <= ? AND s_order.oId = ?");
			Connection con = JDBCutil.getConnection();
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, begindt);
				pst.setString(2, enddt);
				pst.setInt(3, k);
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					total = rs.getInt("total");
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}				
		} catch (NumberFormatException eo) {
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT COUNT(s_accountant.aId) AS total FROM s_accountant");
			sql.append(" INNER JOIN s_order ON s_order.oId = s_accountant.oId");
			sql.append(" INNER JOIN s_orderclass ON s_orderclass.oClassId = s_order.oClassId");
			sql.append(" INNER JOIN s_employee ON s_employee.eId = s_order.eId");
			sql.append(" WHERE s_order.oTime >= ? AND s_order.oTime <= ? AND (");
			sql.append(" s_orderclass.oClassName LIKE ? OR s_orderclass.oClassType LIKE ?");
			sql.append(" OR s_employee.eName LIKE ?)");
			Connection con = JDBCutil.getConnection();
			try {
				PreparedStatement pst = con.prepareStatement(sql.toString());
				pst.setString(1, begindt);
				pst.setString(2, enddt);
				pst.setString(3, "%"+key+"%");
				pst.setString(4, "%"+key+"%");
				pst.setString(5, "%"+key+"%");
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					total = rs.getInt("total");
				}
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
					
		return total;
	}

}
